package com.h3bpm.kumhosunny.crm.controller.budget;

import OThinker.Common.Organization.Models.User;
import OThinker.H3.Controller.ControllerBase;
import com.alibaba.fastjson.JSONObject;
import com.h3bpm.kumhosunny.common.utils.ImportExcelUtil;
import com.h3bpm.kumhosunny.common.utils.SqlUtil;
import data.DataException;
import data.DataRow;
import data.DataTable;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.UUID;

/**
 * @authormi
 * @date2019/9/6
 */
@Controller
@RequestMapping("/Portal/CRM/Budget")
public class BudgetController extends ControllerBase {

    private static final Logger LOGGER = LoggerFactory.getLogger(BudgetController.class);

    private final String STATUS= "500";
    private final String STE= "500";
    private final int three =3 ;
    private final int two =2 ;
    private final int four =4 ;

    @Override
    public String getFunctionCode() {
        return null;
    }

    DecimalFormat df = new DecimalFormat("#.00");


    /**
     *获取可用预算金额
     * @param date  预计出差日期
     * @param userId  用户id
     * @return
     */
    @RequestMapping(value = "/getBudget", method = RequestMethod.GET, produces = {
            "application/json;charset=UTF-8" })
    @ResponseBody
    public JSONObject getBudget(String date , String userId){

         JSONObject result = new JSONObject();
        result.put("status","200");
        result.put("message","成功");
        String[] dateStr = date.split("-");
        String  year =dateStr[0];
        String month = dateStr[1];
        try {
        String getDubgetSql = "select * from k_dubgetexpand where userId = '"+userId+"' and byear='"+year+"' and  bmonth='"+month+"'";
        DataTable dataDubget = null;
        dataDubget = SqlUtil.doQuery(getDubgetSql);
            if(dataDubget!=null && dataDubget.getEntityRows()!=null && dataDubget.getEntityRows().size()>0) {
                DataRow dataRow = dataDubget.getEntityRows().get(0);
                result.put("availableBudget", dataRow.getString("availableBudget"));
                result.put("dyMonthBudget", dataRow.getString("dyMonthBudget"));
                return result;
            }else{
                result.put("status","500");
                result.put("message","此人没有预算记录");
                return result;
            }

    } catch (Exception e) {
            result.put("status","500");
            result.put("message","系统异常");
        e.printStackTrace();
    }
            return result;

    }




    /**
     * 批量导入预算
     * @param request
     * @param response
     * @return
     */
    @RequestMapping(value = "/importBudget", method = RequestMethod.POST, produces = {
            "application/json;charset=UTF-8" })
    @ResponseBody
    public JSONObject importBudget(HttpServletRequest request, HttpServletResponse response) throws Exception {

        JSONObject result = null;

        MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
        InputStream in = null;
        List<List<Object>> budgetList = null;
        MultipartFile file = multipartRequest.getFile("pic");
        if (file.isEmpty()) {
            LOGGER.error("文件不存在");
            result.put("status", 500);
            result.put("message", "文件不存在！");
            throw new Exception("文件不存在！");
        }
            in = file.getInputStream();
            budgetList = ImportExcelUtil.getBankListByExcel(in, file.getOriginalFilename());
            in.close();
            //写入平铺表
          result = writeBudgetShowTable(budgetList);
            if(!STATUS.equals(result.getString(STE))) {
                //写入预算相关表
                result = wirteBudgetTable(budgetList);
                 new BudgetSumController().updatePracticalAvailableBudget();

            }

        return result;
    }

     //写入预算相关表
    public JSONObject wirteBudgetTable( List<List<Object>> budgetList ){
        JSONObject result = new JSONObject();
        result.put("message","导入成功");
        result.put("status","200");
        SimpleDateFormat formatData= new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String userObjectId = getUserValidator().getUser().getObjectID();
        try {
        for (int i = 0; i < budgetList.size(); i++) {
            List<Object>  data = budgetList.get(i);

            //取1月数据
            String january=getMonthMoney(data,  3);
            //取2月数据
            String february=getMonthMoney(data,  4);
            //取3月数据
            String march=getMonthMoney(data,  5);
            //取4月数据
            String april=getMonthMoney(data,  6);
            //取5月数据
            String may=getMonthMoney(data,  7);
            //取6月数据
            String june=getMonthMoney(data,  8);
            //取7月数据
            String july=getMonthMoney(data,  9);
            //取8月数据
            String august=getMonthMoney(data,  10);
            //取9月数据
            String september = getMonthMoney(data,  11);
            //取10月数据
            String october = getMonthMoney(data,  12);
            //取11月数据
            String  november = getMonthMoney(data,  13);
            //取12月数据
            String december = getMonthMoney(data,  14);
            String userCode = "";
            String byear = "";
            //取科目
            String subject = "";
            //取工号
            if(data.get(0)!=null && !"".equals(data.get(0).toString())){
                userCode= data.get(0).toString();
            }
            //取年份
            if(data.get(2)!=null && !"".equals(data.get(2).toString())){
                byear= data.get(2).toString();
                if(byear.indexOf(".")!=-1){
                    byear= byear.substring(0,byear.indexOf("."));
                }
            }
            //第一季度总预算
            BigDecimal quarterBudget1= new BigDecimal(0);
            //第二季度总预算
            BigDecimal quarterBudget2= new BigDecimal(0);
            //第三季度总预算
            BigDecimal quarterBudget3= new BigDecimal(0);
            //第四季度总预算
            BigDecimal quarterBudget4= new BigDecimal(0);
            //判断季度当前季度每个月的预算不可以为空
            //第一季度
            if(!"".equals(january) && !"".equals(february) && !"".equals(march)){
                quarterBudget1 = quarterBudget1.add(new BigDecimal(january)).add(new BigDecimal(february)).add(new BigDecimal(march));
            }
            //第二季度
            if(!"".equals(april) && !"".equals(may) && !"".equals(june)){
                quarterBudget2 = quarterBudget2.add(new BigDecimal(april)).add(new BigDecimal(may)).add(new BigDecimal(june));
            }
            //第三季度
            if(!"".equals(july) && !"".equals(august) && !"".equals(september)){
                quarterBudget3 = quarterBudget3.add(new BigDecimal(july)).add(new BigDecimal(august)).add(new BigDecimal(september));
            }
            //第四季度
            if(!"".equals(october) && !"".equals(november) && !"".equals(december)){
                quarterBudget4 = quarterBudget4.add(new BigDecimal(october)).add(new BigDecimal(november)).add(new BigDecimal(december));
            }
            User user =  getEngine().getOrganization().GetUserByCode(userCode);
            //新增第一季度
            if(Double.parseDouble(quarterBudget1.toString())>0){
                //第1季度1月
                updateBudgetUserRow(  userCode ,    user ,  "01" ,"1",  quarterBudget1.toString() ,  byear,   january ,  userObjectId ,  formatData.format(new Date()) ,  subject);
                //第1季度2月
                updateBudgetUserRow(  userCode ,   user ,  "02" ,"1",  quarterBudget1.toString() ,  byear,   february,  userObjectId ,  formatData.format(new Date()) ,  subject);
               //第1季度3月
                updateBudgetUserRow(  userCode ,   user ,  "03" ,"1",  quarterBudget1.toString() ,  byear,   march,  userObjectId ,  formatData.format(new Date()) ,  subject);
            }
            //新增第二季度
            if(Double.parseDouble(quarterBudget2.toString())>0){
                //第2季度4月
                updateBudgetUserRow(  userCode ,   user ,  "04" ,"2",  quarterBudget2.toString() ,  byear,   april ,  userObjectId ,  formatData.format(new Date()) ,  subject);
                //第2季度5月
                updateBudgetUserRow(  userCode ,   user ,  "05" ,"2",  quarterBudget2.toString() ,  byear,   may,  userObjectId ,  formatData.format(new Date()) ,  subject);
                //第2季度6月
                updateBudgetUserRow(  userCode ,   user ,  "06" ,"2",  quarterBudget2.toString() ,  byear,   june,  userObjectId ,  formatData.format(new Date()) ,  subject);
            }
            //新增第三季度
            if(Double.parseDouble(quarterBudget3.toString())>0){
                //第3季度7月
                updateBudgetUserRow(  userCode ,    user ,  "07" ,"3",  quarterBudget3.toString() ,  byear,   july ,  userObjectId ,  formatData.format(new Date()) ,  subject);
                //第3季度8月
                updateBudgetUserRow(  userCode ,   user ,  "08" ,"3",  quarterBudget3.toString() ,  byear,   august,  userObjectId ,  formatData.format(new Date()) ,  subject);
                //第3季度9月
                updateBudgetUserRow(  userCode ,    user ,  "09" ,"3",  quarterBudget3.toString() ,  byear,   september,  userObjectId ,  formatData.format(new Date()) ,  subject);
            }
            if(Double.parseDouble(quarterBudget4.toString())>0){  //新增第四季度
                //第4季度10月
                updateBudgetUserRow(  userCode ,   user ,  "10" ,"4",  quarterBudget4.toString() ,  byear,   october ,  userObjectId ,  formatData.format(new Date()) ,  subject);
                //第4季度11月
                updateBudgetUserRow(  userCode ,   user ,  "11" ,"4",  quarterBudget4.toString() ,  byear,   november,  userObjectId ,  formatData.format(new Date()) ,  subject);
                //第4季度12月
                updateBudgetUserRow(  userCode , user ,  "12" ,"4",  quarterBudget4.toString() ,  byear,   december,  userObjectId ,  formatData.format(new Date()) ,  subject);
            }
        }
        } catch (Exception e) {
            LOGGER.error("系统异常" + e.toString());
            result.put("message","系统异常");
            result.put("status","500");
            e.printStackTrace();
        }
        return result ;
    }



    /*
    修改预算表内容逻辑
     */
    public void updateBudgetUserRow(String userCode  ,User user ,String month ,String quarter,String quarterBudget ,String byear, String monthMoney ,String userObjectId ,String time ,String subject) throws Exception {

        String objectId = UUID.randomUUID().toString();
        String insrtBudgetSql = " insert into k_budget  (objectId,orgId,userId,userCode,belongToYear,quarter,quarterBudget,belongToMonth,monthBudget,CreatedTime,CreatedBy,ModifiedTime,ModifiedBy,subject) VALUES ";
        String insrtBudgetExpandSql = " insert into k_dubgetexpand (objectId,budgetTableId,availableBudget,applicationBudget,occupationBudget,lockBudget,deployBudget,CreatedTime,CreatedBy,ModifiedTime,ModifiedBy,bmonth,byear,subject,userId,lastMonthMoney,dyMonthBudget) VALUES ";

        //查询当前月份预算是否存在
        DataTable dataTable =  SqlUtil.doQuery(" select * from k_budget where  userId = '"+user.getObjectID()+"' and belongToMonth = '"+month+"' and belongToYear = '"+byear+"' ");
        if(dataTable==null || dataTable.getEntityRows()==null || dataTable.getEntityRows().size()<1) {
            //查询当前月份预算 存在则新增
           String insertValuesFeild= " ( '"+objectId+"' , '"+user.getParentID()+"','"+user.getObjectID()+"','"+userCode+"','"+byear+"','"+quarter+"','"+quarterBudget+"','"+month+"','"+monthMoney+"','"+time+"','"+userObjectId+"','"+time+"','"+userObjectId+"','"+subject+"' ) ";
           //新增预算
           SqlUtil.doNoQuery(insrtBudgetSql +insertValuesFeild );
            DataTable dubgetDataTable =  SqlUtil.doQuery(" select * from k_budget where  userCode = '"+userCode+"' and belongToMonth = '"+month+"' and belongToYear = '"+byear+"'");
            String objectIdExpand = UUID.randomUUID().toString();
           String insertExpandValuesFeild= "  ( '"+objectIdExpand+"' , '"+dubgetDataTable.getEntityRows().get(0).getString("objectId")+"','"+monthMoney+"','0','0','0','0','"+time+"','"+userObjectId+"','"+time+"','"+userObjectId+"','"+month+"','"+byear+"','"+subject+"','"+user.getObjectID()+"','','"+monthMoney+"')";
            SqlUtil.doNoQuery(insrtBudgetExpandSql+insertExpandValuesFeild);
        }

    }






    public JSONObject writeBudgetShowTable( List<List<Object>> budgetList) {

        JSONObject result = new JSONObject();
        result.put("message","导入成功");
        result.put("status","200");

        try {
            String userObjectId = getUserValidator().getUser().getObjectID();
            String userOrgObjectId =getUserValidator().getUser().getParentID();
            SimpleDateFormat formatData= new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String insrtBudgetShowSqlField = "insert into i_budget  (ObjectID, June,byear,CreatedTime,May,September,March,ParentPropertyName,ModifiedBy,userCode,October,December,OwnerId,ModifiedTime,July,January,February,November,CreatedBy,team,userName,April,August,CreatedByParentId,OwnerParentId,subject) VALUES ";
            String insertValuesStr="";


            for (int i = 0; i < budgetList.size(); i++) {
                List<Object>  data = budgetList.get(i);
                String objectId = UUID.randomUUID().toString();
                //取1月数据
                String january=getMonthMoney(data,  3);
                //取2月数据
                String february=getMonthMoney(data,  4);
                //取3月数据
                String march=getMonthMoney(data,  5);
                //取4月数据
                String april=getMonthMoney(data,  6);
                //取5月数据
                String may=getMonthMoney(data,  7);
                //取6月数据
                String june=getMonthMoney(data,  8);
                //取7月数据
                String july=getMonthMoney(data,  9);
                //取8月数据
                String august=getMonthMoney(data,  10);
                //取9月数据
                String september = getMonthMoney(data,  11);
                //取10月数据
                String october = getMonthMoney(data,  12);
                //取11月数据
                String  november = getMonthMoney(data,  13);
                //取12月数据
                String december = getMonthMoney(data,  14);

               String  subject = "";
              /*  if(data.get(16)!=null && !"".equals(data.get(16).toString())){
                    subject= data.get(16).toString();
                }*/
                String team = "";
                String userCode = "";
                String userName = "";
                String byear = "";
                //取团队
               /* if(data.get(0)!=null && !"".equals(data.get(0).toString())){
                    team= data.get(0).toString();

                }*/
                //取工号
                if(data.get(0)!=null && !"".equals(data.get(0).toString())){
                    userCode= data.get(0).toString();
                    User user =  getEngine().getOrganization().GetUserByCode(userCode);
                    userName=user.getName();

                    if(user==null){
                        result.put("message","用户工号 "+userCode+" 不存在，请仔细核对后导入");
                        result.put("status","500");
                        return result;
                    }

                    team= getEngine().getOrganization().GetUnit(user.getParentID()).getName();
                }else{
                    result.put("message","用户工号 有空缺，请仔细核对后导入");
                    result.put("status","500");
                    return result;
                }

                //取年份
                if(data.get(2)!=null && !"".equals(data.get(2).toString())){
                    byear= data.get(2).toString();
                    if(byear.indexOf(".")!=-1){
                        byear= byear.substring(0,byear.indexOf("."));
                    }

                }else{
                    result.put("message","请输入对应年份：工号"+userCode+"");
                    result.put("status","500");
                    return result;
                }

                String getUserBudgetSql  = " Select * from i_budget where userCode = '"+userCode+"' and  byear = '"+byear+"' ";
                DataTable budgetTable = SqlUtil.doQuery(getUserBudgetSql);
                if(budgetTable!=null && budgetTable.getEntityRows()!=null && budgetTable.getEntityRows().size()>0) {

                    //判断季度当前季度每个月的预算不可以为空
                    //第一季度
                    if((!"".equals(january.trim()) && Double.parseDouble(january.trim())>0) || (!"".equals(february.trim()) && Double.parseDouble(february.trim())>0) || (!"".equals(march.trim()) && Double.parseDouble(march.trim())>0)){
                        if(checkBudget(  budgetTable, (int)1)) {
                            String updateSql = "update i_budget  set subject='" + subject + "', January ='" + january + "' ,February='" + february + "',March='" + march + "'  where userCode ='" + userCode + "' and byear='" + byear + "'";
                            SqlUtil.doNoQuery(updateSql);
                        }else{
                            result.put("message",userCode+"1季度预算已存在");
                        }
                    }
                    //第二季度
                    if((!"".equals(april.trim()) && Double.parseDouble(april.trim())>0) || (!"".equals(may.trim()) && Double.parseDouble(may.trim())>0) || (!"".equals(june.trim()) && Double.parseDouble(june.trim())>0)){
                        if(checkBudget(  budgetTable, (int)2)) {
                            String updateSql = "update i_budget  set subject='" + subject + "', April ='" + april + "' ,May='" + may + "',June='" + june + "'  where userCode ='" + userCode + "' and byear='" + byear + "'";
                            SqlUtil.doNoQuery(updateSql);
                        }else{
                            result.put("message",userCode+"2季度预算已存在");
                        }
                    }
                    //第三季度
                    if((!"".equals(july.trim()) && Double.parseDouble(july.trim())>0) || (!"".equals(august.trim()) && Double.parseDouble(august.trim())>0) || (!"".equals(september.trim()) && Double.parseDouble(september.trim())>0)){
                        if(checkBudget(  budgetTable, (int)3)) {
                            String updateSql = "update i_budget  set  subject='" + subject + "', July ='" + july + "' ,August='" + august + "',September='" + september + "'  where userCode ='" + userCode + "' and byear='" + byear + "'";
                            SqlUtil.doNoQuery(updateSql);
                        }else{
                            result.put("message",userCode+"3季度预算已存在");
                        }
                    }
                    //第四季度
                    if((!"".equals(october.trim()) && Double.parseDouble(october.trim())>0) || (!"".equals(november.trim()) && Double.parseDouble(november.trim())>0) || (!"".equals(december.trim()) && Double.parseDouble(december.trim())>0)){
                        if(checkBudget(  budgetTable, (int)4)) {
                            String updateSql = "update i_budget  set subject='" + subject + "', October ='" + october + "' ,November='" + november + "',December='" + december + "'  where userCode ='" + userCode + "' and byear='" + byear + "'";
                            SqlUtil.doNoQuery(updateSql);
                        }else{
                            result.put("message",userCode+"4季度预算已存在");
                        }
                    }
                }else {
                    String insrtBudgetShowSql = " ('" + objectId + "','" + june + "','" + byear + "','" + formatData.format(new Date()) + "','" + may + "','" + september + "','" + march + "','','" + userObjectId + "','" + userCode + "','" + october + "','" + december + "','" + userObjectId + "','" + formatData.format(new Date()) + "','" + july + "','" + january + "','" + february + "','" + november + "','" + userObjectId + "','"+team+"','"+userName+"','"+april+"','"+august+"','"+ userOrgObjectId + "','" + userOrgObjectId + "','"+subject+"')";
                    if(i==budgetList.size()-1) {
                        insertValuesStr += insrtBudgetShowSql;
                    }else{
                        insertValuesStr += insrtBudgetShowSql + ",";

                    }
                }

            }
            if(!"".equals(insertValuesStr)){
                insrtBudgetShowSqlField += " "+insertValuesStr;
                SqlUtil.doNoQuery(insrtBudgetShowSqlField);
            }

        } catch (Exception e) {
            LOGGER.error("系统异常" + e.toString());
            result.put("message","系统异常");
            result.put("status","500");
            e.printStackTrace();
        }

         return result;
    }


    public boolean checkBudget(DataTable budgetTable, int quarter) throws DataException {

       DataRow dataRow =  budgetTable.getEntityRows().get(0);
        if(quarter==1){
            Double january =0.0;
            Double february =0.0;
            Double march =0.0;
            if(dataRow.getObject("January")!=null && !"".equals(dataRow.getString("January")) ){
                january = dataRow.getDouble("January");
            }
            if(dataRow.getObject("February")!=null && !"".equals(dataRow.getObject("February"))  ) {
                february = dataRow.getDouble("February");
            }
            if(dataRow.getObject("March")!=null && !"".equals(dataRow.getObject("March")) ) {
                march = dataRow.getDouble("March");
            }

            Double result = january+february+march;
            if(result > 0){
                return false;
            }
        }
        if(quarter==two){

            Double april =0.0;
            Double may =0.0;
            Double june =0.0;
            if(dataRow.getObject("April")!=null && !"".equals(dataRow.getString("April")) ){
                april = dataRow.getDouble("April");
            }
            if(dataRow.getObject("May")!=null && !"".equals(dataRow.getObject("May"))  ) {
                may = dataRow.getDouble("May");
            }
            if(dataRow.getObject("June")!=null && !"".equals(dataRow.getObject("June")) ) {
                june = dataRow.getDouble("June");
            }

            Double result = april+may+june;
            if(result > 0){
                return false;
            }
        }
        if(quarter==three){
            Double july =0.0;
            Double august =0.0;
            Double september =0.0;
            if(dataRow.getObject("July")!=null && !"".equals(dataRow.getString("July")) ){
                  july = dataRow.getDouble("July");
            }
            if(dataRow.getObject("August")!=null && !"".equals(dataRow.getObject("August"))  ) {
                  august = dataRow.getDouble("August");
            }
            if(dataRow.getObject("September")!=null && !"".equals(dataRow.getObject("September")) ) {
                  september = dataRow.getDouble("September");
            }

            Double result =  july+august+september;
            if(result > 0){
                return false;
            }
        }
        if(quarter==four){
            Double october =0.0;
            Double november =0.0;
            Double december =0.0;
            if(dataRow.getObject("October")!=null && !"".equals(dataRow.getString("October")) ){
                october = dataRow.getDouble("October");
            }
            if(dataRow.getObject("November")!=null && !"".equals(dataRow.getObject("November"))  ) {
                november = dataRow.getDouble("November");
            }
            if(dataRow.getObject("December")!=null && !"".equals(dataRow.getObject("December")) ) {
                december = dataRow.getDouble("December");
            }

            Double result = october+november+december;
            if(result > 0){
                return false;
            }
        }


        return true;
    }


      public String getMonthMoney(List<Object>  data,int index){

        String monthMoney = "";
          if(  data.size()>index && data.get(index)!=null && !"".equals(data.get(index) )){
              double result = new BigDecimal(data.get(index).toString()).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
              monthMoney= result+"";
          }

          return monthMoney;
      }


}
